package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.adempiere.exceptions.DBException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.Mhrmfitnessmilitary;
import org.compiere.model.Mhrmmilitaryrank;
import org.compiere.model.Mhrmmilitaryregistration;
import org.compiere.model.Mhrmstructure;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;
import org.eevolution.model.X_HR_Employee;
import org.eevolution.model.X_HR_Job;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class MilitaryRegistrationPrint extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int structure_ID = 0;
	/** */
	private int militaryrank_ID = 0;
	/** */
	X_HR_Department block = null;
	/** */
	 X_HR_Department department = null;
	/** */
	private String path = "";
	/**	Employee List	*/
	private MBPartner[] m_lines;

	@Override
	protected void prepare() {

		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("hrm_structure_ID") == 0 && para[i].getParameter() != null)
				structure_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("hrm_militaryrank_ID") == 0 && para[i].getParameter() != null)
				militaryrank_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "SampleMilitary";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Military registration").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableCellFormat borderStyle = new WritableCellFormat();
	    borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    borderStyle.setAlignment(Alignment.CENTRE);
	    borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    borderStyle.setWrap(true);
	    // Deprtment style cell
	    WritableFont fontNormal = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
	    WritableCellFormat cellDeprtment = new WritableCellFormat(fontNormal);
	    cellDeprtment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    cellDeprtment.setAlignment(Alignment.CENTRE);
	    cellDeprtment.setVerticalAlignment(VerticalAlignment.CENTRE);
	    cellDeprtment.setBackground(Colour.TAN);
	    // Title style cell
	    WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
	    WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
	    titleStyle.setAlignment(Alignment.CENTRE);
	    titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		
		 // set title
		 if(HR_Block_ID != 0)
			 label = new Label(1, 3, "Блока "+block.getName(), titleStyle);
		 else
			 label = new Label(1, 3, "");
		 
		 sheet.addCell(label);
	    
	    X_HR_Employee he = null;
	    X_HR_Department department = null;
	    m_lines = getPartnerEmployee(0, HR_Block_ID, HR_Department_ID, structure_ID, militaryrank_ID);
	    Mhrmmilitaryregistration hrmMilitary = null;
	    Mhrmstructure hrmStructure = null;
	    Mhrmfitnessmilitary hrmFitness = null;
	    Mhrmmilitaryrank hrmRank = null;
		int col = cellStart.getC()+1; // offset by the number of first columns
	    int row = cellStart.getR();
	    int dId = 0; // Departmnet ID
	    int dep = 0;
	    int num = 1;
	    int i = 0;
		for(int j = row; j < (row+m_lines.length); j++){
			
			// Title
		    he = X_HR_Employee.getHR_Employee(m_lines[i].getC_BPartner_ID(),true); 
		    X_HR_Job job = new X_HR_Job(getCtx(), he.getHR_Job_ID(), null);
		    department = new X_HR_Department(getCtx(), he.getHR_Department_ID(), null);
		    //
		    hrmMilitary = Mhrmmilitaryregistration.getMilitaryRegistration(m_lines[i].getC_BPartner_ID());
		    hrmStructure = new Mhrmstructure(getCtx(),hrmMilitary.gethrm_structure_ID(),get_TrxName());
		    hrmFitness = new Mhrmfitnessmilitary(getCtx(), hrmMilitary.gethrm_fitnessmilitary_ID(), get_TrxName());
		    hrmRank = new Mhrmmilitaryrank(getCtx(), hrmMilitary.gethrm_militaryrank_ID(), get_TrxName());
		    if(dId != he.getHR_Department_ID()){
		    	   sheet.insertRow(j+dep);
		    	   sheet.mergeCells(1, j+dep, 11, j+dep);
		    	   // Department
		    	   label = new Label(1, j+dep, department.getName(), cellDeprtment); 
			       sheet.addCell(label);
			       department = null;
		    	   dId = he.getHR_Department_ID();
		    	   dep++;
		    }
		    
		    // #
		    number = new Number(cellStart.getC(), j+dep,num, borderStyle); 
			sheet.addCell(number);
		    // Department
		    label = new Label(col, j+dep, m_lines[i].getName(), borderStyle); 
			sheet.addCell(label);
			// Job
			label = new Label(col+1, j+dep, job.getName(), borderStyle); 
			sheet.addCell(label);
			// Structure
			label = new Label(col+2, j+dep, hrmStructure.getName(), borderStyle); 
			sheet.addCell(label);
			// Military Rank
			label = new Label(col+3, j+dep, hrmRank.getName(), borderStyle); 
			sheet.addCell(label);
			// Fitness Military
			label = new Label(col+4, j+dep, hrmFitness.getName(), borderStyle); 
			sheet.addCell(label);
			// 
			label = new Label(col+5, j+dep, hrmMilitary.gethrm_recordkeepinggroup(), borderStyle); 
			sheet.addCell(label);
			// 
			label = new Label(col+6, j+dep, hrmMilitary.gethrm_recordkeepingcategory(), borderStyle); 
			sheet.addCell(label);
			// 
			label = new Label(col+7, j+dep, hrmMilitary.gethrm_militaryspeciality(), borderStyle); 
			sheet.addCell(label);
			// 
			label = new Label(col+8, j+dep, hrmMilitary.gethrm_defenceadministrname(), borderStyle); 
			sheet.addCell(label);
			// 
			label = new Label(col+9, j+dep, hrmMilitary.gethrm_recordkeepingno(), borderStyle); 
			sheet.addCell(label);

			
			i++; num++;
		}
		
		copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();
	       
	    // Open the file with the application associated
	 	String fileName = fullPath.toString();
	 	Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}
	
	/**
	 * 	Get Bpartner (Employee) list
	 * 	@param whereClause starting with AND
	 * 	@return lines
	 */
	private MBPartner[] getPartnerEmployee (int bpartner_id, int block_id, int department_id, int structure_ID, int militaryrank_ID)
	{
		
		MBPartner bp = null;
		
		String sql = "SELECT C_BPARTNER.* FROM C_BPARTNER \n" +
				 "JOIN HR_EMPLOYEE ON HR_EMPLOYEE.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID \n" +
				 "AND HR_EMPLOYEE.ENDDATE = (SELECT MAX(H.ENDDATE) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=HR_EMPLOYEE.C_BPARTNER_ID) \n" +
				 "LEFT JOIN HRM_MILITARYREGISTRATION ON HRM_MILITARYREGISTRATION.C_BPARTNER_ID=HR_EMPLOYEE.C_BPARTNER_ID \n" +
				 "WHERE C_BPARTNER.ISACTIVE='Y' AND C_BPARTNER.ISEMPLOYEE='Y' \n" +
				 "AND C_BPARTNER.AD_CLIENT_ID=? \n";
	
		List<Object> param = new ArrayList<Object>();
		param.add(new BigDecimal(getAD_Client_ID()));
		
		if (bpartner_id != 0){
			sql += " AND C_BPARTNER.C_BPARTNER_ID=? \n";
			param.add(bpartner_id);
		}
		else if (bpartner_id == 0 && department_id != 0){
			sql += " AND HR_EMPLOYEE.HR_DEPARTMENT_ID=? \n";
			param.add(department_id);
		}
		else if(bpartner_id == 0 && block_id !=0){
			sql += " AND HR_EMPLOYEE.HR_DEPARTMENT_ID IN (SELECT NODE_ID FROM ADEMPIERE.AD_TREENODEDEP WHERE PARENT_ID=?) \n";
			param.add(block_id);
			
		}
		if(structure_ID != 0){
			sql += " AND hrm_militaryregistration.hrm_structure_id=? \n";
			param.add(structure_ID);
		}

		sql += "ORDER BY HR_EMPLOYEE.HR_DEPARTMENT_ID";
		
		List<MBPartner> list = new ArrayList<MBPartner>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement (sql, get_TrxName());
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();
			while (rs.next ())
			{
				bp = new MBPartner(getCtx(), rs, get_TrxName());
				list.add(bp);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql, e);
			throw new DBException(e, sql);
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return list.toArray(new MBPartner[list.size()]);
	}

}
